
ALTER        proc sp_products_list_with_customer_part_numbers
@customer_id  int
as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200),Customer_PartNumber varchar(200),Label_Number varchar(200),ODM_ID INT)
	declare @proc_id varchar (20)
	declare @mft_part_no varchar (50)
	declare @mkt_part_no varchar (50)
	declare @prod_id int
	declare @p_type varchar (50)
	declare @p_description varchar (200)
	declare @customer_partnumber varchar(200)
	declare @label_number varchar(200)
	declare @odm_id int

	insert into @results values(null,'---- CHIPS -----','---- CHIPS -----',null,null,null,null,null,null)

	declare ncursor cursor for
	--select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type is null and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.P_Marketing_part_Number
	select ps.processstep_id, p.part_number, p.p_marketing_part_number, p.product_id, p.p_type, p.p_comments, cp.customer_partnumber, cp.odm_id from product p inner join process_step ps  on p.processstep_id = ps.processstep_id and p.p_type is null and p.is_approved = 0 and ps_type_name like 'finished goods' and p.processstep_id = ps.processstep_id left outer join customer_partnumbers cp on p.product_id = cp.product_id and cp.customer_id = @customer_id order by part_number, odm_id

	open ncursor
		fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description, @customer_partnumber, @odm_id
		--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		while @@fetch_status = 0
		begin
			if ( @odm_id is not null )
			begin
				-- Added by Veeru on 22nd Dec 2004 to check whether the relation is active or inactive
				if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_ID = 2 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
					and  ( select count(*) from Customer_ODM_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and ODMCustomer_ID = @odm_id ) > 0 )
				begin
					insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
				end

				else if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_Id = 3 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
					and  ( select count(*) from Customer_Endcustomer_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and EndCustomer_ID = @odm_id ) > 0 )
				begin
					insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
				end
				-- End of Added by Veeru
			end
			else
			begin
				--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id and odm_id is null
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
			end

			set @customer_partnumber = null
			set @label_number = null
			set @odm_id = null
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null

			fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description, @customer_partnumber, @odm_id
			--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		end
	close ncursor
	deallocate ncursor

	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null,null,null,null)

	declare bcursor cursor for
	--select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = -1 order by Product.P_Marketing_part_Number
	select ps.processstep_id, p.part_number, p.p_marketing_part_number, p.product_id, p.p_type, p.p_comments, cp.customer_partnumber, cp.odm_id from product p inner join process_step ps  on p.processstep_id = ps.processstep_id and p.p_type = 'BOARD' and p.is_approved = 0 and ps_type_name like 'finished goods' and p.processstep_id = ps.processstep_id and p.P_Parent_Product_ID = -1 left outer join customer_partnumbers cp on p.product_id = cp.product_id and cp.customer_id = @customer_id order by part_number, odm_id
	open bcursor

		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id
		--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		while @@fetch_status = 0
		begin
			-- Added by Prabhanjan (15 Apr 2004)
			declare childCursor cursor for
			--select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = @prod_id order by Product.P_Marketing_part_Number
			select ps.processstep_id, p.part_number, p.p_marketing_part_number, p.product_id, p.p_type, p.p_comments, cp.customer_partnumber, cp.odm_id from product p inner join process_step ps  on p.processstep_id = ps.processstep_id and p.p_type = 'BOARD' and p.is_approved = 0 and ps_type_name like 'finished goods' and p.processstep_id = ps.processstep_id and p.P_Parent_Product_ID = @prod_id left outer join customer_partnumbers cp on p.product_id = cp.product_id and cp.customer_id = @customer_id order by part_number, odm_id
			open childCursor
			fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id
			-- If child boards not found insert parent board details
			if @@fetch_status <> 0
			begin
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
			end
			while @@fetch_status = 0
			begin
				if ( @odm_id is not null )
				begin
					--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id and odm_id = @odm_id
					-- Added by Veeru on 22nd Dec 2004 to check whether the relation is active or inactive
					if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_ID = 2 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
						and  ( select count(*) from Customer_ODM_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and ODMCustomer_ID = @odm_id ) > 0 )
					begin
						insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
					end

					else if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_ID = 3 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
						and ( select count(*) from Customer_Endcustomer_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and EndCustomer_ID = @odm_id ) <> 0 )
					begin
						insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
					end
					-- End of Added by Veeru
				end
				else
				begin
					--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id and odm_id is null
					insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
				end
				set @customer_partnumber = null
				set @label_number = null
				set @odm_id = null
				set @proc_id = null
				set @mft_part_no = null
				set @mkt_part_no = null
				set @prod_id = null
				set @p_type = null
				set @p_description = null
				fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id

			end
			close childCursor
			deallocate childCursor

			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			set @label_number = null
		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id
		--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		end
	close bcursor
	deallocate bcursor

	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null,null,null,null)
	-- Added another condition (and p_parent_product_id <> -1) as child part numbers are introduced in Kits ravikanth

	declare kcursor cursor for
	--select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'KIT' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.P_Marketing_part_Number
	select ps.processstep_id, p.part_number, p.p_marketing_part_number, p.product_id, p.p_type, p.p_comments, cp.customer_partnumber, cp.odm_id from product p inner join process_step ps  on p.processstep_id = ps.processstep_id and p.p_type = 'KIT' and p.is_approved = 0 and ps_type_name like 'finished goods' and p.processstep_id = ps.processstep_id left outer join customer_partnumbers cp on p.product_id = cp.product_id and cp.customer_id = @customer_id order by part_number, odm_id
	--SELECT inv.ProcessStep_ID ProcessStep_ID,IST_Manufacturing_Part_Number,IST_Marketing_Part_Number,product.Product_ID, P_Type FROM Inventory_Step_Table inv ,Product,Process_Step  where Process_Step.Ps_Type = 'Finished Goods' and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID and p_type = 'KIT'
	open kcursor
		fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id
		--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		while @@fetch_status = 0
		begin

			if ( @odm_id is not null )
			begin
				--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id and odm_id = @odm_id
				-- Added by Veeru on 22nd Dec 2004 to check whether the relation is active or inactive
				if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_ID = 2 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
					and  ( select count(*) from Customer_ODM_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and ODMCustomer_ID = @odm_id ) > 0 )
				begin
					insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
				end

				else if ( ( select count(*) from customer_type_relationship ctr, customer_types ct where customer_id = @odm_id and ct.C_Type_ID = 3 and ct.C_Type_ID = ctr.C_Type_ID ) > 0
					and  ( select count(*) from Customer_Endcustomer_Relationship cor, customer_relationship_status crs where crs.name = 'Active' and crs.RelationShipStatus_Id = cor.RelationShipStatus_Id and ParentCustomer_ID = @Customer_Id and EndCustomer_ID = @odm_id ) > 0 )
				begin
					insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
				end
				-- End of Added by Veeru

			end
			else
			begin
				--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id and odm_id is null
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number,@odm_id)
			end

			set @customer_partnumber = null
			set @label_number = null
			set @odm_id = null

			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description, @customer_partnumber, @odm_id
			--select @customer_partnumber = customer_partnumber,@label_number = label_number from customer_partNumbers where product_id = @prod_id and customer_id = @customer_id
		end
	close kcursor
	deallocate kcursor
end
select * from @results

